package com.entrogy.dao;

import com.alibaba.fastjson.JSONObject;
import com.entrogy.bean.CourseEntity;
import com.entrogy.bean.StaticCourseEntity;
import com.entrogy.bean.UserEntity;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.mapping.FetchType;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Repository
public interface CourseMapper {

    @Insert("insert into course_information(course_name,school_code,class_id,static_course_id,course_id,gride_id,course_total_score) values(#{courseName},#{schoolCode},#{classId},#{staticCourseId},#{courseId},#{gradeId},#{courseTotalScore})")
    int addCourse(@Param("courseName") String courseName, @Param("classId") int classId, @Param("schoolCode") String schoolCode, @Param("courseId") int courseId, @Param("staticCourseId") int staticCourseId, @Param(value = "gradeId") Integer gradeId, @Param("courseTotalScore") Double courseTotalScore);

    @Update("update course_information set course_name = #{courseName},course_id = #{course_id}  where  id = #{id}")
    int updateCourse(@Param("id") int id,@Param("courseName") String courseName,@Param("course_id") String course_id);

    @Select("<script> select c.id as id,concat(cl.class_name,'',sc.course_name) as courseName,c.class_id as classId,c.gride_id as grideId,cl.class_name as className,c.static_course_id as staticId,c.course_id as courseId from course_information c "
            +"LEFT JOIN class_information cl ON (cl.id=c.`class_id`  AND cl.`school_code`=c.`school_code`) "
            +"INNER JOIN static_course sc on c.static_course_id=sc.id"
            +"<where>"
            +"  <if test='course_name != null and course_name != \"\" '>c.course_name like CONCAT('%',#{course_name},'%')</if>"
            +"  <if test='grade_id != null and grade_id != \"\"'> and cast(c.class_id as char) like '%${grade_id}%'</if>"
            +"  <if test='class_id != null and class_id != \"\"'> and c.class_id = #{class_id}</if>"
            +"  <if test='schoolCode != null and schoolCode != \"\"'> and c.school_code = #{schoolCode}</if>"
            +"</where> "
            +"</script>")
    List<CourseEntity> getCourseList(@Param("course_name") String courseName,@Param("grade_id") String gradeId,@Param("class_id") String classId,@Param("schoolCode") String schoolCode);

    @Delete("delete from course_information where school_code = #{schoolCode} and id = #{id}")
    int deleteCourse(@Param("id")int id,@Param("schoolCode") String schoolCode);

    @Select("<script> select count(*) from course_information c "
                +"LEFT JOIN class_information cl ON (cl.id=c.`class_id`  AND cl.`school_code`=c.`school_code`)"
                +"<where>"
                +"  <if test='course_name != null and course_name != \"\" '>c.course_name like CONCAT('%',#{course_name},'%')</if>"
                +"  <if test='grade_id != null and grade_id != \"\"'> and cast(c.class_id as char) like '%${grade_id}%' </if>"
                +"  <if test='class_id != null and class_id != \"\"'> and c.class_id = #{class_id}</if>"
                +"  <if test='schoolCode != null and schoolCode != \"\"'> and c.school_code = #{schoolCode}</if>"
                +"</where> "
            +"</script>")
    int getTotal(@Param("course_name") String courseName,@Param("grade_id") String gradeId,@Param("class_id") String classId,@Param("schoolCode") String schoolCode);

    @Select("select id,course_name,static_course_id as staticId,class_id,school_code,gride_id  from course_information where id = #{courseId}")
    CourseEntity getCourseEntity(@Param("courseId") int courseId);

    @Select("select teacher_id from course_information where id = #{courseId} and school_code = #{schoolCode}")
    String getCourseById(@Param("courseId") int courseId, @Param("schoolCode") String schoolCode);

    @Update("update course_information set teacher_id = #{teacherId},gride_id = #{gradeId} where id = #{courseId} and school_code = #{schoolCode}")
    int accountAssignment(@Param("courseId") int courseId, @Param("teacherId") String teacherId, @Param("schoolCode") String schoolCode, @Param("gradeId")Integer gradeId);

    @Select("select * from course_information where #{teacherId} in (select teacher_id from course_information where school_code = #{schoolCode} )")
    List<CourseEntity> getCourseByTeacherId(@Param("teacherId") String teacherId,@Param("schoolCode") String schoolCode);

    @Results({
            @Result(property="courseEntityList",column = "{teacherId=id,schoolCode=school_code}",many=@Many(select="com.entrogy.dao.CourseMapper.getCourseByTeacherId",fetchType= FetchType.LAZY))
    })
    @Select("select id,name,school_code from user where school_code = #{schoolCode}")
    List<UserEntity> getUserAccount(@Param("schoolCode") String schoolCode);

    @Insert("insert into static_course(course_name,uid,school_code) values(#{courseName},#{uid},#{schoolCode})")
    int insertStaticCourse(@Param("courseName") String courseName,@Param("uid") String uid, @Param("schoolCode") String schoolCode);

    @Update("update static_course set course_name = #{courseName},uid = #{uid} where id = #{id} and school_code = #{schoolCode}")
    int updateStaticCourse(@Param("id") int id,@Param("courseName") String courseName, @Param("uid") String uid,@Param("schoolCode") String schoolCode);

    @Select("select * from static_course where school_code = #{schoolCode}")
    List<StaticCourseEntity> getStaticCourse(@Param("schoolCode") String schoolCode);

    /**
     * 获取某个学校的课程名称列表
     * @param schoolCode
     * @return
     */
    @Select("select course_name  from static_course where school_code = #{schoolCode}")
    List<String> getStaticCourseName(@Param("schoolCode") String schoolCode);

    /**
     *  获取某个学校的课程ID
     * @param schoolCode
     * @return
     */
    @Select("select course_id  from course_information where school_code = #{schoolCode}")
    List<Integer> getIdBySchoolCode(@Param("schoolCode") String schoolCode);

    /**
     *  根据id 查询课程名称
     * @return
     */
    @Select("select course_name   from static_course where school_code = #{schoolCode} and id=#{staticCourseId} ")
    String getCourseNameById(@Param("schoolCode") String schoolCode,@Param("staticCourseId")Integer staticCourseId);

    /**
     *   查询用户信息
     * @param schoolCode
     * @return
     */
    @Select("<script>"
            +"select id,name   from user where school_code = #{schoolCode} and delete_status=0 "
            +"  <if test='name != null and name != \"\" '> and name LIKE \'%${name}%\' </if>"
            +"</script>")
    List<Map<String,Object>>  getUserList(@Param("schoolCode") String schoolCode,@Param("name")String name);

    /**
     *   查询用户信息总数
     * @param schoolCode
     * @return
     */
    @Select("<script>"
            +"select count(id)  from user where school_code = #{schoolCode} and delete_status=0 "
            +"  <if test='name != null and name != \"\" '> and name LIKE \'%${name}%\' </if>"
            +"</script>")
    int  getUserListTotal(@Param("schoolCode")String schoolCode,@Param("name")String name);

    /**
     *  获取教师id
     * @param schoolCode
     * @return
     */
    @Select("select id,course_name as courseName,teacher_id as teacherId  from course_information where school_code = #{schoolCode} ")
    List<CourseEntity> getTeacherId(@Param("schoolCode")String schoolCode);

    /**
     * 修改teaherid
     * @param id
     * @param teacherId
     * @return
     */
    @Update("update  course_information  set  teacher_id=#{teacherId} where id=#{id}")
    int   updateTeacherId(@Param("id")int id,@Param("teacherId")String teacherId);

    /**
     * 根据年级查询所有课程
     * @param gradeId
     * @param schoolCode
     * @return
     */
    @Select("\n" +
            "select DISTINCT sc.course_name,sc.id from static_course sc left join course_information ci on sc.id=ci.static_course_id where ci.gride_id=#{gradeId} and sc.school_code=#{schoolCode}")
    List<StaticCourseEntity> getCourseListByGradeId(@Param("gradeId")String gradeId,@Param("schoolCode")Integer schoolCode);

    /**
     * 根据课程id查询班级id
     * @param courseId
     * @return
     */
    @Select("select class_id from course_information where id=#{courseId} ")
    Integer findClassIdByCourseId(String courseId);

    /**
     * 查询一个班级所拥有的静态科目
     * @param classId
     * @param schoolCode
     * @return
     */
    @Select("select static_course_id from course_information where class_id=#{classId} and school_code=#{schoolCode}")
    List<Integer> listStaticCourseByClassId(@Param(value = "classId") int classId, @Param(value = "schoolCode") Integer schoolCode);

    /**
     * 查询该学校的所有静态科目
     * @param schoolCode
     * @return
     */
    @Select("select id,course_name,school_code,uid from static_course where school_code=#{schoolCode}")
    List<StaticCourseEntity> listStaticCourse(Integer schoolCode);

    /**
     * 查询导入的excel数据
     * @param schoolCode
     * @param gradeId
     * @param classId
     * @return
     */
    @Select("<script>"
            + "select grade_name as gradeName,school_code as schoolCode,class_id as classId,course_name as courseName,teacher_name as teacherName "
            + "from course_excel where school_code=#{schoolCode}"
            + "  <if test='gradeId != null'> and grade_id=#{gradeId} </if>"
            + "  <if test='classId != null'> and class_id=#{classId} </if>"
            + "</script>")
    List<JSONObject> listCourseExcelData(@Param("schoolCode") Integer schoolCode, @Param("gradeId") Integer gradeId, @Param("classId") Integer classId);

    @Select("insert into course_information(course_id,course_name,class_id,school_code,gride_id,teacher_id,static_course_id) values(#{course_id},#{course_name},#{class_id},#{schoolCode},#{grade_id},#{teacher_id},#{static_course_id})")
    void insertCourse(@Param("course_id") String course_id,
                      @Param("course_name") String course_name, @Param("class_id") String class_id,
                      @Param("schoolCode") Integer schoolCode, @Param("grade_id") String grade_id,
                      @Param("teacher_id") String teacher_id, @Param("static_course_id") Integer static_course_id);

    /**
     * 查询教师所教科目
     * @param teacherId
     * @return
     */
    @Select("select ci.id,ci.course_name,class_id,gride_id from course_information ci where ci.teacher_id like concat('%', #{teacherId}, '%')")
    List<CourseEntity> listTeacherCourse(String teacherId);

    /**
     * 查询静态课程的id
     * @param schoolCode
     * @param courseName
     * @return
     */
    @Select("select sc.id from static_course sc where sc.course_name=#{courseName} and sc.school_code=#{schoolCode}")
    Integer getStaticCourseId(@Param("schoolCode") Integer schoolCode, @Param("courseName") String courseName);


}
